package Database;

import java.util.*;
import java.sql.*;

public class loyalty 
{
	private Connection conn = null;
	private PreparedStatement prepSt = null;
	private dbConnection db;
	private ResultSet rSet;
	private java.sql.Date date;
	
	public loyalty()
	{
		db = new dbConnection();
		conn = db.openDB();
	}
	
	public void createLoyaltyTable()
	{
		try
		{
			String create = "CREATE TABLE Loyalty " + "(cardID NUMBER PRIMARY KEY, points NUMBER, expire_Date DATE, " +
					"start_Date DATE, customerID NUMBER, FOREIGN KEY(customerID) REFERENCES Customer(customerID))";
			prepSt = conn.prepareStatement(create);
			prepSt.executeUpdate();
			
			String insert = "INSERT INTO Loyalty(cardID,points,expire_Date,start_Date,customerID) values(?,?,?,?,?)";
			prepSt = conn.prepareStatement(insert);
			
			prepSt.setInt(1, 1);
			prepSt.setInt(2, 20);
			//prepSt.setDate(3, x);
			//prepSt.setDate(4, x);			
			prepSt.setInt(5, 1);
			prepSt.executeUpdate();
			
			conn.commit();
			prepSt.close();
		}
		catch(Exception e)
		{
			System.out.println("SQL Exception " +e);
			System.exit(0);
		}
	}
	
	public void dropLoyaltyTable()
	{
		try
		{
			String drop = "DROP TABLE Loyalty";
			prepSt = conn.prepareStatement(drop);
			prepSt.executeUpdate();
			System.out.println();
			System.out.println("Table dropped");
			conn.commit();
			prepSt.close();
		}
		catch(Exception e)
		{
			System.out.println("SQL Exception " +e);
			System.exit(0);
		}
	}
	
	public void queryLoyalty()
	{
		try
		{
			String query = "SELECT * FROM Loyalty";
			prepSt = conn.prepareStatement(query);
			rSet = prepSt.executeQuery();
			System.out.println();
			System.out.println("ID\tPoints\t\tExpire Date \t\tStart Date\t\tCustomer ID");
			while (rSet.next())
			{
				System.out.println(rSet.getInt(1)+ "\t" +rSet.getString(2)+ "\t\t"
						+rSet.getString(3)+ "\t\t" +rSet.getString(4)+ "\t" +rSet.getString(5));
			}
			prepSt.close();
			rSet.close();
		}
		catch(Exception e)
		{
			System.out.println("SQL Exception " +e);
			System.exit(0);
		}
	}
	
	public static void main(String[] args) 
	{
		loyalty thisQ = new loyalty();
		thisQ.createLoyaltyTable();
		thisQ.queryLoyalty();
		thisQ.dropLoyaltyTable();
		thisQ.db.closeDB();
	}
}
